package com.wind.sql;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Locale;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.wind.bean.LatBean;
import com.wind.bean.SqlDatabean;

public class DBTotalTables {
	private String TAG = "sptest";
	private String date = null;
	private String time = null;

	private Cursor cur;
	private String SelectTable = "SELECT * FROM ";
	private String Where = " WHERE ";
	private String AND_Left = "' AND ";
	private String AND_Right = " AND '";
	private String AND_NO = " AND ";
	private String UpdateTable = "UPDATE ";
	private String UpdateSet = " SET ";

	private String InsertTable = "INSERT INTO ";
	private String Start = " (";
	private String End = ") ";
	// ",region INTEGER,number INTEGER,sqrt TEXT,mark TEXT,other1 TEXT,other2
	// TEXT,latitude DOUBLE, longitude DOUBLE,type TEXT)
	private String InsertColumnTotal = "region,number,sqrt,mark,other1,other2,latitude, longitude,type";
	private String Value = "values('";
	private String split = "','";

	private String Between = " BETWEEN '";
	private String AND_B = "' AND '";

	/**
	 * 输入上传下载数值，对指定的表进行更新
	 * 
	 * @param mySQL
	 *            进行修改的数据库
	 * @param table
	 *            数据库的表：有wifi，mobile等
	 * @param upload
	 *            记录上传流量
	 * @param download
	 *            记录下载流量
	 */
	public void getCurrentData(SQLiteDatabase mySQL, String table, long upload,
			long download) {
		StringBuilder string = new StringBuilder();
		// select oldest upload and download 之前记录的数据的查询操作
		// SELECT * FROM table WHERE type=0
		string = string.append(SelectTable).append(table).append(Where)
				.append("type=").append(0);
		try {
			cur = mySQL.rawQuery(string.toString(), null);
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
		long oldup0 = -50;
		long olddown0 = -50;
		if (cur != null) {
			try {
				int minup = cur.getColumnIndex("upload");
				int mindown = cur.getColumnIndex("download");
				// showLog(cur.getColumnIndex("minute") ).append( "");
				if (cur.moveToFirst()) {
					// 获得之前的上传下载值
					oldup0 = cur.getLong(minup);
					olddown0 = cur.getLong(mindown);
				}
			} catch (Exception e) {
				Log.d(TAG, "数据库搜索失败");
				oldup0 = -100;
				olddown0 = -100;
			}
		}
		// showLog("oldup0=" + oldup0 + "olddown0=" + olddown0);
		if (cur != null) {
			cur.close();
		}

		if (oldup0 != -100) {
			if (oldup0 < 0)
				oldup0 = 0;
			if (olddown0 < 0)
				olddown0 = 0;
			cur = null;
			// 初始化写入数据（wifi以及mobile）
			// 如果之前数据大于新的数据，则重新计数
			if ((oldup0 > (upload + 10000)) || (olddown0 > (download + 10000))) {
				oldup0 = upload;
				olddown0 = download;
			} else {
				oldup0 = upload - oldup0;
				olddown0 = download - olddown0;
			}
			// showLog("oldup0 up=" + oldup0 + "olddown0 down=" + olddown0);
			if ((olddown0 != 0 || oldup0 != 0)
					&& ((olddown0 > 512) || (oldup0 > 512))) {
				string = new StringBuilder();
				string = string.append(SelectTable).append(table).append(Where)
						.append("date='").append(date).append(AND_Left)
						.append("type=").append(2);
				try {
					cur = mySQL.rawQuery(string.toString(), null);
				} catch (Exception e) {
					Log.d(TAG, string.toString() + "fail" + e);
				}
				long oldup2 = 0;
				long olddown2 = 0;
				String olddate2 = "";
				// 进行添加 覆盖+
				// showLog("cur.move" + cur.moveToFirst());
				if (cur.moveToFirst()) {
					// showLog("已有单天数据");
					try {
						int minup = cur.getColumnIndex("upload");
						int mindown = cur.getColumnIndex("download");
						int dateIndex = cur.getColumnIndex("date");
						// showLog(cur.getColumnIndex("minute") + "");
						if (cur.moveToFirst()) {
							// 获得之前的上传下载值
							oldup2 = cur.getLong(minup);
							olddown2 = cur.getLong(mindown);
							olddate2 = cur.getString(dateIndex);
						}
					} catch (Exception e) {
						Log.d(TAG, "cur-searchfail");
						oldup2 = 0;
						olddown2 = 0;
						olddate2 = "";
					}
					// showLog(oldup2+"olddown2="+olddown2+olddate2);
					// 3为日统计流量
					if (olddate2 != "") {
						updateSQLtotalType2(mySQL, table, oldup2 + oldup0,
								olddown2 + olddown0, 2);
						updateSQLtotalType0(mySQL, table, upload, download, 0);
						// 时刻对于的数据
						// exeSQLtotalSetData(mySQL, table, oldup0, olddown0, 2,
						// other);
						// if (table == "mobile") {
						// SharedPrefrenceData sharedData = new
						// SharedPrefrenceData(
						// context);
						// long beforemobile = sharedData
						// .getMonthHasUsedStack();
						// if (beforemobile == -100) {
						// beforemobile = 0;
						// }
						// beforemobile = beforemobile + oldup0 + olddown0;
						// sharedData.setMonthHasUsedStack(beforemobile);
						// long beforeToday = sharedData
						// .getTodayMobileDataLong();
						// beforeToday = beforeToday + oldup0 + olddown0;
						// sharedData.setTodayMobileDataLong(beforeToday);
						// }

					}
					// 进行添加add
				} else {
					// showLog("无单天数据");
					updateSQLtotalType0to2(mySQL, table, oldup0, olddown0, 0, 2);
					insertSQLtotal(mySQL, table, upload, download, 0);
					// updateSQLtotalType(mySQL, table, upload, download, 0,
					// other, 0);
					// 时刻对于的数据
					// exeSQLtotalSetData(mySQL, table, oldup0, olddown0, 2,
					// other);
					// SharedPrefrenceData sharedData = new SharedPrefrenceData(
					// context);
					// if (table == "mobile") {
					// long beforemobile = sharedData.getMonthHasUsedStack();
					// if (beforemobile == -100) {
					// beforemobile = 0;
					// }
					// beforemobile = beforemobile + oldup0 + olddown0;
					// sharedData.setMonthHasUsedStack(beforemobile);
					// long beforeToday = sharedData.getTodayMobileDataLong();
					// beforeToday = beforeToday + oldup0 + olddown0;
					// sharedData.setTodayMobileDataLong(beforeToday);
					// }
				}
				if (cur != null) {
					cur.close();
				}
			}
		}
	}

	/**
	 * 对当天的type=0的数据进行更新，记录当前读取的流量数据值
	 * 
	 * 
	 * @param mySQL
	 *            进行写入操作的数据库SQLiteDatagase
	 * @param table
	 *            数据库的表：有wifi，mobile等 * @param upload 记录上传流量
	 * @param download
	 *            记录下载流量
	 * @param type
	 *            修改type=0的数据，
	 */
	private void updateSQLtotalType0(SQLiteDatabase mySQL, String table,
			long upload, long download, int type) {
		StringBuilder string = new StringBuilder();
		string = string.append(UpdateTable).append(table).append(UpdateSet)
				.append("date='").append(date).append("',time='").append(time)
				.append("',upload='").append(upload).append("',download='")
				.append(download).append("'").append(Where).append("type=")
				.append(type);
		// UPDATE Person SET
		// date='date',time='time',upload='upload',download='download'
		// ,type='typechange' WHERE type=type
		try {
			mySQL.execSQL(string.toString());
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
	}

	/**
	 * 对当天的type=2的数据进行更新，添加新纪录读取的流量
	 * 
	 * 
	 * @param mySQL
	 *            进行写入操作的数据库SQLiteDatagase
	 * @param table
	 *            数据库的表：有wifi，mobile等 * @param upload 记录上传流量
	 * @param download
	 *            记录下载流量
	 * @param type
	 *            修改type=2的数据，
	 */
	private void updateSQLtotalType2(SQLiteDatabase mySQL, String table,
			long upload, long download, int type) {
		StringBuilder string = new StringBuilder();
		string = string.append(UpdateTable).append(table).append(UpdateSet)
				.append("time='").append(time).append("',upload='")
				.append(upload).append("',download='").append(download)
				.append("'").append(Where).append("date='").append(date)
				.append(AND_Left).append("type=").append(type);
		// UPDATE Person SET
		// date='date',time='time',upload='upload',download='download'
		// ,type='typechange' WHERE type=type
		try {
			mySQL.execSQL(string.toString());
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
	}

	/**
	 * 
	 * 将type=0的那条数据更新成type=2，作为前一天的流量记录保存
	 * 
	 * 
	 * @param mySQL
	 *            进行写入操作的数据库SQLiteDatagase
	 * @param table
	 *            数据库的表：有wifi，mobile等
	 * @param upload
	 *            记录上传流量
	 * @param download
	 *            记录下载流量
	 * @param type
	 *            获取type=0的数据，
	 * @param typechange
	 *            将type=0 修改成type=2
	 */
	private void updateSQLtotalType0to2(SQLiteDatabase mySQL, String table,
			long upload, long download, int type, int typechange) {
		StringBuilder string = new StringBuilder();
		string = string.append(UpdateTable).append(table).append(UpdateSet)
				.append("time='").append(time).append("',upload='")
				.append(upload).append("',download='").append(download)
				.append("' ,type=").append(typechange).append(" ,date='")
				.append(date).append("'").append(Where).append("type=")
				.append(type);
		// UPDATE Person SET
		// date='date',time='time',upload='upload',download='download'
		// ,type='typechange' WHERE type=type
		try {
			mySQL.execSQL(string.toString());
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
	}

	/**
	 * 依据数值插入新数据--old
	 * 
	 * @param mySQL
	 *            进行写入操作的数据库SQLiteDatagase
	 * @param table
	 *            数据库的表：有wifi，mobile等
	 * @param upload
	 *            记录上传流量
	 * @param download
	 *            记录下载流量
	 * @param type
	 *            设置type值
	 */
	private void insertSQLtotal(SQLiteDatabase mySQL, String table,
			long upload, long download, int type) {
		StringBuilder string = new StringBuilder();
		string = string.append(InsertTable).append(table).append(Start)
				.append(InsertColumnTotal).append(End).append(Value)
				.append(date).append(split).append(time).append(split)
				.append(upload).append(split).append(download).append(split)
				.append(type).append("'" + End);
		// INSERT INTO t4 (date,time,upload,download,uid,type) VALUES
		// ('date','time','upload','download','uid','type')
		try {
			mySQL.execSQL(string.toString());
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
	}

	/**
	 * 生成最新的日期与时间
	 */
	public void initTime() {
		long timeMillis = System.currentTimeMillis();
		SimpleDateFormat dateFormate = new SimpleDateFormat("yyyy-MM-dd",
				Locale.CHINA);
		SimpleDateFormat timeFormate = new SimpleDateFormat("HH:mm:ss",
				Locale.CHINA);
		date = dateFormate.format(timeMillis);
		time = timeFormate.format(timeMillis);
	}

	/**
	 * 进行Wifi历史流量查询
	 * 
	 * @param sqlDataBase
	 *            sqlDataBase
	 * @param year
	 *            输入查询的年份2000.
	 * @param month
	 *            输入查询的月份.
	 * @return 返回一个64位数组。a[0]为总计上传流量a[63]为总计下载流量
	 *         a[1]-a[31]为1号到31号上传流量，a[32]-a[62]为1号到31号下载流量
	 */
	public long[] SelectWifiData(SQLiteDatabase sqlDataBase, int year, int month) {
		return SelectData(sqlDataBase, year, month, DBManager.WIFI_TABLE);
	}

	/**
	 * 进行移动数据流量历史流量查询
	 * 
	 * @param sqlDataBase
	 *            sqlDataBase
	 * @param year
	 *            输入查询的年份2000.
	 * @param month
	 *            输入查询的月份.
	 * @return 返回一个64位数组。a[0]为总计上传流量a[63]为总计下载流量
	 *         a[1]-a[31]为1号到31号上传流量，a[32]-a[62]为1号到31号下载流量
	 */
	// specialfortext TableWiFi- TableMobile
	public long[] SelectMobileData(SQLiteDatabase sqlDataBase, int year,
			int month) {
		return SelectData(sqlDataBase, year, month, DBManager.MOBILE_TABLE);
	}

	/**
	 * 进行数据流量历史流量查询
	 * 
	 * @param sqlDataBase
	 *            sqlDataBase
	 * @param year
	 *            输入查询的年份2000.
	 * @param month
	 *            输入查询的月份.
	 * @param table
	 *            要查询的数据类型
	 * @return 返回一个64位数组。a[0]为总计上传流量a[63]为总计下载流量
	 *         a[1]-a[31]为1号到31号上传流量，a[32]-a[62]为1号到31号下载流量
	 */
	private long[] SelectData(SQLiteDatabase sqlDataBase, int year, int month,
			String table) {
		long[] a = new long[64];
		String month2 = month + "";
		if (month < 10)
			month2 = "0" + month2;
		// showLog(month2);
		StringBuilder string = new StringBuilder();
		// select oldest upload and download 之前记录的数据的查询操作
		// SELECT * FROM table WHERE type=0
		string = string.append(SelectTable).append(table).append(Where)
				.append("date").append(Between).append(year).append("-")
				.append(month2).append("-").append("01").append(AND_B)
				.append(year).append("-").append(month2).append("-")
				.append("31").append(AND_Left).append("type=").append(3);
		// showLog(string);
		try {
			cur = sqlDataBase.rawQuery(string.toString(), null);
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
		String newdate = "";
		String countdate = "";
		String dateStr1 = year + "-" + month2 + "-" + "0";
		String dateStr2 = year + "-" + month2 + "-";
		long newup = 0;
		long newdown = 0;
		int i = 1;
		if (cur != null) {
			try {
				int dateIndex = cur.getColumnIndex("date");
				int uploadIndex = cur.getColumnIndex("upload");
				int downloadIndex = cur.getColumnIndex("download");
				// showLog(cur.getColumnIndex("minute") + "");
				if (cur.moveToFirst()) {
					do {
						if (i < 10)
							countdate = dateStr1 + i;
						else
							countdate = dateStr2 + i;
						newdate = cur.getString(dateIndex);
						newup = cur.getLong(uploadIndex);
						newdown = cur.getLong(downloadIndex);
						if (newdate.equals(countdate)) {
							// 记录每日数据
							a[i] += newup;
							a[i + 31] += newdown;
						} else {
							// 记录累计数据
							a[0] += a[i];
							a[63] += a[i + 31];
							while (!newdate.equals(countdate)) {
								// 用于跨越天数
								i++;
								if (i < 10)
									countdate = dateStr1 + i;
								else
									countdate = dateStr2 + i;
								if (i > 31) {
									// 如果天数顺序不正确，进行恢复
									for (int j = 1; j < 32; j++) {
										if (j < 10)
											countdate = dateStr1 + j;
										else
											countdate = dateStr2 + j;
										if (newdate.equals(countdate)) {
											i = j;
											break;
										}
										;
									}
									break;
								}

							}
							// 记录每日数据
							a[i] += newup;
							a[i + 31] += newdown;
						}
					} while (cur.moveToNext());
				}
				// 记录累计数据
				a[0] += a[i];
				a[63] += a[i + 31];
			} catch (Exception e) {
				Log.d(TAG, "datacur-searchfail");
			}
		}
		if (cur != null) {
			cur.close();
		}
		// for (int j = 0; j < a.length; j++) {
		// showLog(j + "liuliang" + a[j] + "");
		// }
		return a;
	}

	public void insertSqlBeanArray(SQLiteDatabase mySQL,
			ArrayList<SqlDatabean> sqlBeanArray) {
		for (SqlDatabean sqlDatabean : sqlBeanArray) {
			insertSqlBean(mySQL, sqlDatabean);
		}
	}

	public void insertSqlBean(SQLiteDatabase mySQL, SqlDatabean sqlBean) {
		// "region,number,sqrt,mark,other1,other2,latitude, longitude,type";
		// type=center：0,bound：1
		int region = sqlBean.getRegion();
		int number = sqlBean.getNumber();
		String sqrt = sqlBean.getSqrtString();
		String mark = sqlBean.getMarkString();
		String other1 = sqlBean.getOtherString1();
		String other2 = sqlBean.getOtherString2();

		LatBean center = sqlBean.getCenter();
		ArrayList<LatBean> lats = sqlBean.getLats();
		StringBuilder string = new StringBuilder();
		string = string.append(InsertTable).append(DBManager.AREA_TABLE)
				.append(Start).append(InsertColumnTotal).append(End)
				.append(Value).append(region).append(split).append(number)
				.append(split).append(sqrt).append(split).append(mark)
				.append(split).append(other1).append(split).append(other2)
				.append(split).append(center.getLatitude()).append(split)
				.append(center.getLongitude()).append(split).append(0)
				.append("'" + End);
		try {
			mySQL.execSQL(string.toString());
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
		for (LatBean latBean : lats) {
			string = new StringBuilder();
			string = string.append(InsertTable).append(DBManager.AREA_TABLE)
					.append(Start).append(InsertColumnTotal).append(End)
					.append(Value).append(region).append(split).append(number)
					.append(split).append(sqrt).append(split).append(mark)
					.append(split).append(other1).append(split).append(other2)
					.append(split).append(latBean.getLatitude()).append(split)
					.append(latBean.getLongitude()).append(split).append(1)
					.append("'" + End);
			try {
				mySQL.execSQL(string.toString());
			} catch (Exception e) {
				Log.d(TAG, string.toString() + "fail" + e);
			}
		}

	}

	public SqlDatabean searchDB(SQLiteDatabase sqlDataBase, int region,
			int number) {
		SqlDatabean bean = new SqlDatabean();
		LatBean center = new LatBean();
		LatBean lattemp = new LatBean();
		ArrayList<LatBean> lats = new ArrayList<LatBean>();
		bean.setNumber(number);
		bean.setRegion(region);

		StringBuilder string = new StringBuilder();
		// select oldest upload and download 之前记录的数据的查询操作
		// SELECT * FROM table WHERE type=0
		// "region,number,latitude, longitude,type";
		string = string.append(SelectTable).append(DBManager.AREA_TABLE)
				.append(Where).append("region=").append(region).append(AND_NO)
				.append("number=").append(number);
		// showLog(string);
		try {
			cur = sqlDataBase.rawQuery(string.toString(), null);
		} catch (Exception e) {
			Log.d(TAG, string.toString() + "fail" + e);
		}
		if (cur != null) {
			double latitude = 0;
			double longitude = 0;
			String type = null;
			try {
				int latIndex = cur.getColumnIndex("latitude");
				int longIndex = cur.getColumnIndex("longitude");
				int typeIndex = cur.getColumnIndex("type");
				// showLog(cur.getColumnIndex("minute") + "");
				if (cur.moveToFirst()) {
					do {
						latitude = cur.getDouble(latIndex);
						longitude = cur.getDouble(longIndex);
						type = cur.getString(typeIndex);
						if (type.equals("center")) {
							center.setLatitude(latitude);
							center.setLongitude(longitude);
							bean.setCenter(center);
						}
						if (type.equals("bound")) {
							lattemp = new LatBean();
							lattemp.setLatitude(latitude);
							lattemp.setLongitude(longitude);
							lats.add(lattemp);
						}

					} while (cur.moveToNext());
				}
				// 添加lats
				bean.setLats(lats);
			} catch (Exception e) {
				Log.d(TAG, "datacur-searchfail");
			}
		}
		if (cur != null) {
			cur.close();
		}
		// for (int j = 0; j < a.length; j++) {
		// showLog(j + "liuliang" + a[j] + "");
		// }
		return bean;

	}
}
